import pymysql


class DBTools2:
    # 类属性
    # 类属性-连接
    __conn = None
    # 类属性-游标
    __cursor = None

    # 私有类方法：获取连接
    @classmethod
    def __get_conn(cls):
        if cls.__conn is None:
            # 赋值类属性-连接
            cls.__conn = pymysql.connect(host='192.168.174.132',
                                         port=3306, user='root',
                                         password='iTest_2022_mysql',
                                         database='School',
                                         charset='utf8')
        return cls.__conn

    # 私有类方法：获取游标，通过连接
    @classmethod
    def __get_cursor(cls):
        if cls.__cursor is None:
            # 赋值类属性-游标
            cls.__cursor = cls.__get_conn().cursor()
        return cls.__cursor

    # 公有类方法：执行sql，用游标
    @classmethod
    def execute_sql(cls, sql):
        try:
            # 自己的，游标
            cursor = cls.__get_cursor()
            # 自己的，游标执行sql
            cursor.execute(sql)

            # 判断是否select查询语句，查询语句，提取结果集返回，否则提交事务，统计获取操作行记录返回
            # 默认按照空白字符切割sql字符串，返回列表，第一个元素[0],统一转小写lower(),是select语句
            if sql.split()[0].lower() == 'select':
                # 自己的，游标提取，所有结果集返回
                return cursor.fetchall()
            else:
                # 类属性连接，提交事务
                cls.__conn.commit()
                # 自己的，游标获取，统计操作记录行数返回
                return cursor.rowcount

        except Exception as err:
            print("错误信息:", err)
            # 类属性连接，回滚事务
            cls.__conn.rollback()
        finally:
            # 类属性游标，关闭
            cls.__close_cursor()
            # 类属性连接，关闭
            cls.__close_conn()

    # 私有类方法：关闭游标
    @classmethod
    def __close_cursor(cls):
        if cls.__cursor:
            cls.__cursor.close()
            # 关闭后，类属性游标，重新赋值None
            cls.__cursor = None

    # 私有类方法：关闭连接
    @classmethod
    def __close_conn(cls):
        if cls.__conn:
            cls.__conn.close()
            # 关闭后，类属性连接，重新赋值None
            cls.__conn = None


if __name__ == '__main__':
    drop_classes_table_sql = "drop table if exists classes;"
    create_classes_table_sql = 'create table if not exists classes (\
                        id int unsigned auto_increment primary key not null,\
                        name varchar(30) not null);'
    insert_classes_table_sql = 'insert into classes values \
                        ( 0,"ceshi_01期"),\
                        ( 0,"ceshi_02期");'
    drop_students_table_sql = "drop table if exists students;"
    create_students_table_sql = "create table if not exists students(\
                            id int unsigned primary key auto_increment not null,\
                            name varchar(20) default '',\
                            age tinyint unsigned default 0,\
                            height decimal(5,2),\
                            gender enum('男','女','中性','保密') default '保密',\
                            cls_id int unsigned default 0,\
                            is_delete bit default 0\
                        );"
    insert_students_table_sql = "insert into students values\
                        (0,'小明',18,180.00,2,1,0),\
                        (0,'小月月',18,180.00,2,2,1),\
                        (0,'彭于晏',29,185.00,1,1,0),\
                        (0,'刘德华',59,175.00,1,2,1);"
    select_sql_01 = 'select * from students where is_delete=0;'
    select_sql_02 = 'select * from students where  cls_id=(select id from classes where name="ceshi_01期") and is_delete=0;'
    select_sql_03 = "select * from students where age > (select avg(age) from students);"

    print("删除classes表:", DBTools2.execute_sql(drop_classes_table_sql))
    print("创建classes表:", DBTools2.execute_sql(create_classes_table_sql))
    print("插入classes表:", DBTools2.execute_sql(insert_classes_table_sql))

    print("删除students表:", DBTools2.execute_sql(drop_students_table_sql))
    print("创建students表:", DBTools2.execute_sql(create_students_table_sql))
    print("插入students表:", DBTools2.execute_sql(insert_students_table_sql))

    print("查询没被删除的学生:", DBTools2.execute_sql(select_sql_01))
    print("查询班级为‘ceshi1期’没有被删除的学生:", DBTools2.execute_sql(select_sql_02))
    print("查询大于平均年龄的所有同学:", DBTools2.execute_sql(select_sql_03))

# 删除classes表: 0
# 创建classes表: 0
# 插入classes表: 2
# 删除students表: 0
# 创建students表: 0
# 插入students表: 4
# 查询没被删除的学生: ((1, '小明', 18, Decimal('180.00'), '女', 1, b'\x00'), (3, '彭于晏', 29, Decimal('185.00'), '男', 1, b'\x00'))
# 查询班级为‘ceshi1期’没有被删除的学生: ((1, '小明', 18, Decimal('180.00'), '女', 1, b'\x00'), (3, '彭于晏', 29, Decimal('185.00'), '男', 1, b'\x00'))
# 查询大于平均年龄的所有同学: ((4, '刘德华', 59, Decimal('175.00'), '男', 2, b'\x01'),)